﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using Vkontakte.Api.Database;

namespace VkClientIM.Database
{
	sealed class ClientDatabase
		:IPlacesCache
	{
		private SQLiteConnection _sql;

		public ClientDatabase(string filename)
		{
			var builder = new SQLiteConnectionStringBuilder
			{
				BinaryGUID = true,
				DataSource = filename
			};
			string connectionString = builder.ToString();
			_sql = new SQLiteConnection(connectionString);
			_sql.Open();
		}

		private void CreateTables()
		{
			#region Страны
			var command = _sql.CreateCommand();
			command.CommandText = "CREATE TABLE [Country] ([CountryID] INTEGER NOT NULL PRIMARY KEY,[Title] NVARCHAR(100) NOT NULL)";
			command.ExecuteNonQuery();
			#endregion

			#region Города
			command = _sql.CreateCommand();
			command.CommandText =
				"CREATE TABLE [City] ([CityID] INTEGER NOT NULL PRIMARY KEY,[CountryID] INTEGER NULL,[Title] NVARCHAR(200) NOT NULL)";
			command.ExecuteNonQuery();
			#endregion


		}

		public static ClientDatabase OpenOrCreate(string filename)
		{
			if (File.Exists( filename ))
			{
				return new ClientDatabase( filename );
			}
			else
			{
				ClientDatabase ret = new ClientDatabase(filename);
				ret.CreateTables();
				return ret;
			}
		}

		public bool IsCountriesInited
		{
			get
			{
				SQLiteCommand command = _sql.CreateCommand();
				command.CommandText = "SELECT COUNT(*) FROM [Country]";
				object o = command.ExecuteScalar();
				return ((long) o) != 0;
			}
		}

		public string GetCountryName(int countryID)
		{
			SQLiteCommand command = _sql.CreateCommand();
			command.CommandText = string.Format("SELECT [Title] FROM [Country] WHERE [CountryID]='{0}'", countryID);
			object o = command.ExecuteScalar();
			return o as string;
		}

		public void SaveCountryName(int countryID, string title)
		{
			SQLiteCommand command = _sql.CreateCommand();
			command.CommandText = "INSERT INTO [Country] VALUES (@cid,@title)";
			SQLiteParameter pCid = new SQLiteParameter("@cid");
			SQLiteParameter pTitle = new SQLiteParameter("@title");
			command.Parameters.Add(pCid);
			command.Parameters.Add(pTitle);
			pCid.Value = countryID;
			pTitle.Value = title;
			command.ExecuteNonQuery();
		}

		public string GetCityName(int cityID)
		{
			throw new NotImplementedException();
		}

		public void SaveCityName(int cityID,int countryID, string title)
		{
			SQLiteCommand command = _sql.CreateCommand();
			command.CommandText = "INSERT INTO [Country] VALUES (@cityid,@cid,@title)";
			SQLiteParameter pCityid = new SQLiteParameter("@cityid");
			SQLiteParameter pCid = new SQLiteParameter("@cid");
			SQLiteParameter pTitle = new SQLiteParameter("@title");
			command.Parameters.Add(pCityid);
			command.Parameters.Add(pCid);
			command.Parameters.Add(pTitle);
			pCityid.Value = cityID;
			pCid.Value = countryID;
			pTitle.Value = title;
			command.ExecuteNonQuery();
		}
	}
}
